Re: How do concurrent inserts work?

--As of December 27, 2014 12:37:28 PM -0700, Yaroslav is alleged to have
said:

> Tom Lane-2 wrote
>> The current coding chooses to give you a "duplicate key" error on
>> the grounds that that's more helpful than a generic "serialization
>> failure" error.
>
> But it seems counterintuitive. PostgreSQL reports that there is
> conflicting row, so I look... and don't see it! Surprising, IMHO.
>
> But why is it more helpful?
>
> It seems that in this situation, if I need, for example, to insert or
> update this row (if it exists), my transaction is doomed anyway. So if I
> got "serialization failure", I wouldn't even try to 'ROLLBACK TO
> SAVEPOINT', as it's pointless (right?). With "duplicate key" error, I may
> decide that committed row actually exists and try to update it in vain.

--As for the rest, it is mine.

On the other hand, with a 'duplicate key' error, you could in theory
generate a new key (if that's possible) and succeed, where with a
'serialization failure' error you wouldn't know that's an option; it could
be *any* serialization failure in any of the fields/rows being updated.

Neither's perfect.  I can see the arguments for this one - it's the more
specific error, so you can try to deal with it, but it also 'leaks'
serialization, which leads to your issues.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------